package com.inspinia.base.util;

import com.inspinia.base.common.model.BaseConstants;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.PaneInformation;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
 * 主要提供对Excel的各种处理,侧重点是取数据
 *
 * @author zhangyuan
 * @date 2018/4/10
 */
public class PoiExcelUtil {

    protected static Logger logger = LoggerFactory.getLogger(PoiExcelUtil.class);
    static FastDateFormat sFormat = FastDateFormat.getInstance(BaseConstants.DATE_TIME_PATTERN, Locale.ENGLISH);
    static short[] yyyyMMdd = {14, 31, 57, 58, 179, 184, 185, 186, 187, 188};
    static short[] HHmmss = {20, 32, 190, 191, 192};
    static List<short[]> yyyyMMddList = Arrays.asList(yyyyMMdd);
    static List<short[]> hhMMssList = Arrays.asList(HHmmss);

    /**
     * 根据路径,获取WorkBook对象
     *
     * @param filePath 文件路径
     * @return workbook
     * @throws Exception
     */
    public static Workbook getExcelWorkbook(String filePath) throws Exception {
        Workbook workbook = null;
        File file = new File(filePath);
        if (file.exists()) {
            workbook = getWorkBookByStream(new FileInputStream(file));
        }
        return workbook;
    }

    /**
     * 根据输入流ins获取WorkBook对象
     *
     * @param ins 输入流
     * @return workbook
     * @throws Exception
     */
    public static Workbook getWorkBookByStream(InputStream ins) throws Exception {
        return WorkbookFactory.create(ins);
    }

    /**
     * 根据Workbook,sheetIndex获取sheet对象
     *
     * @param book WorkBook对象
     * @param number sheetIndex ,从1开始
     * @return sheet
     * @throws Exception
     */
    public static Sheet getSheetByNum(Workbook book, int number) throws Exception {
        return book.getSheetAt(number - 1);
    }

    /**
     * 根据 Workbook对象返回该Workbook对象中所有sheet的Map数组.
     *
     * @param book
     * @return Map<sheetIndex , sheetName>
     * @throws Exception
     */
    public static Map<Integer, String> getSheetNameByBook(Workbook book) throws Exception {
        Map<Integer, String> map = new LinkedHashMap<Integer, String>();
        int sheetNum = book.getNumberOfSheets();
        for (int indexSheet = 1; indexSheet <= sheetNum; indexSheet++) {
            Sheet sheet = getSheetByNum(book, indexSheet);
            map.put(indexSheet, sheet.getSheetName());
        }
        return map;
    }

    /**
     * 获取workbook数据Map集合
     *
     * @param book
     * @return Map<Integer, List<List<String>>> @
     * throws Exception
     */
    public static Map<Integer, List<List<String>>> getWorkbookDatas(Workbook book) throws Exception {
        Map<Integer, List<List<String>>> bookdatas = new HashMap<Integer, List<List<String>>>();
        int sheetNum = book.getNumberOfSheets();
        for (int indexSheet = 1; indexSheet <= sheetNum; indexSheet++) {
            Sheet sheet = getSheetByNum(book, indexSheet);
            bookdatas.put(indexSheet, getSheetDataList(sheet));
        }
        return bookdatas;
    }

    /**
     * 获取sheet中的数据
     *
     * @param sheet
     * @return List<List<String>> @
     * throws Exception
     */
    public static List<List<String>> getSheetDataList(Sheet sheet) throws Exception {
        List<List<String>> sheetdatas = new ArrayList<List<String>>();
        //需要先合并单元格数据
        mergedRegion(sheet);
        int lastRowNum = getRowNum(sheet);
        int lastCellNum = getColumnNum(sheet);
        for (int i = 0; i < lastRowNum; i++) {
            Row row = sheet.getRow(i);
            sheetdatas.add(getRowDataList(sheet, row, lastCellNum));
        }
        return sheetdatas;
    }

    /**
     * 获取的数据对象是符合easyui格式的标准JSON对象数据集[{A:x,B:xx,C:xxx},{A:x,B:xx,C:xxx}]
     *
     * @param sheet
     * @return
     */
    public static List<Map<String, String>> getSheetDataMap(Sheet sheet) {
        List<Map<String, String>> sheetdatas = new ArrayList<Map<String, String>>();
        int lastRowNum = getRowNum(sheet);
        Row row;
        for (int i = 0; i < lastRowNum; i++) {
            row = sheet.getRow(i);
            Map<String, String> map = getRowDataMap(sheet, row);
            if (!map.isEmpty()) {
                sheetdatas.add(map);
            }
        }
        return sheetdatas;
    }

    /**
     * 获取的数据对象是符合dHtml格式的非标准JSON对象数据集[{id:1 , data:[x,xx,xxx]},{id:2
     * ,data:[x,xx,xxx]}]
     *
     * @param sheet
     * @return
     */
    public static List<Map<String, Object>> getSheetDataMapAndId(Sheet sheet) throws Exception {
        List<Map<String, Object>> sheetdatas = new ArrayList<Map<String, Object>>();
        List<List<String>> sheetLists = getSheetDataList(sheet);
        for (int i = 0; i < sheetLists.size(); i++) {
            Map<String, Object> dataMap = new HashMap<String, Object>(sheetLists.size());
            dataMap.put("id", i);
            dataMap.put("data", sheetLists.get(i));
            sheetdatas.add(dataMap);
        }
        return sheetdatas;
    }

    /**
     * 读取一行的数据,返回的是数据集合List,[x,xx,xxx]
     *
     * @param row
     */
    public static List<String> getRowDataList(Sheet sheet, Row row, int lastCellNum) {
        List<String> rowdatas = new ArrayList<String>();
        if (row == null) {
            for (int i = 0; i < lastCellNum; i++) {
                rowdatas.add("");
            }
        } else {
            for (int i = 0; i < lastCellNum; i++) {
                rowdatas.add(getCellData(row.getCell(i)));
            }
        }
        return rowdatas;
    }

    /**
     * 获取一行的数据集合,体现的是Map<String , String>{A:x,B:xx,C:xxx}
     *
     * @param row
     * @return
     */
    public static Map<String, String> getRowDataMap(Sheet sheet, Row row) {
        Map<String, String> rowdatas = new LinkedHashMap<String, String>();
        String cellVaue;
        int columnNum = 0;
        int lastCellNum = getColumnNum(sheet);
        for (int j = 0; j < lastCellNum; j++) {
            cellVaue = getCellData(row.getCell(j));
            rowdatas.put(getCharByNum(columnNum) + "", cellVaue);
            columnNum = columnNum + 1;
        }
        return rowdatas;
    }

    /**
     * 获取指定Sheet中指定一列的数据.
     *
     * @param sheet 指定的Sheet
     * @param colIndex 指定的列
     * @return
     * @throws Exception
     */
    public static List<String> getColumnDataList(Sheet sheet, int colIndex) throws Exception {
        List<String> coldatas = new ArrayList<String>();
        int lastRowNum = getRowNum(sheet);
        for (int i = 0; i < lastRowNum; i++) {
            coldatas.add(getSheetCellValueWithRowIndexAndColIndex(sheet, i, colIndex));
        }
        return coldatas;
    }

    /**
     * 返回指定sheet页的最大行数,例如:25,则表示下标从0...24
     *
     * @param sheet
     * @return
     */
    public static int getRowNum(Sheet sheet) {
        return sheet.getLastRowNum() + 1;
    }

    /**
     * 返回指定sheet页的最大列数,例如:25,则表示下标从0...24
     *
     * @param sheet
     * @return 列数
     */
    public static int getColumnNum(Sheet sheet) {
        int maxclNum = 0;
        int lastRowNum = getRowNum(sheet);
        for (int i = 0; i < lastRowNum; i++) {
            if (sheet.getRow(i) != null) {
                int tempNum = sheet.getRow(i).getLastCellNum();
                if (tempNum > maxclNum) {
                    maxclNum = tempNum;
                }
            }
        }
        return maxclNum;
    }

    /**
     * 获取单元格的名称 按照excel常见的名称 例如A1
     *
     * @param rowInt 行数 从0开始
     * @param columnInt 列数 从0开始
     * @return String
     */
    public static String getCellName(int rowInt, int columnInt) {
        CellReference cellReference = new CellReference(rowInt, columnInt);
        return cellReference.formatAsString();
    }

    /**
     * 获取指定单元格的行坐标
     *
     * @param cellName 例如A1
     * @return 2
     */
    public static int getCellRowIndex(String cellName) {
        CellReference cellReference = new CellReference(cellName);
        return cellReference.getRow();
    }

    /**
     * 获取指定单元格的列坐标
     *
     * @param cellName 例如A1
     * @return 0
     */
    public static int getCellColIndex(String cellName) {
        CellReference cellReference = new CellReference(cellName);
        return cellReference.getCol();
    }

    /**
     * 获取指定sheet中指定rowNum和cellNum的值
     *
     * @param sheet
     * @param rowNum
     * @param cellNum
     * @return
     * @throws Exception
     */
    public static String getSheetCellValueWithRowIndexAndColIndex(Sheet sheet, int rowNum, int cellNum) throws Exception {
        Row row = sheet.getRow(rowNum);
        Cell cell = row.getCell(cellNum);
        return getCellData(cell);
    }

    /**
     * 获取给定SHEET中指定单元格的值
     *
     * @param sheet 指定SHEET
     * @param cellName 格式为：A1,B3
     * @return
     */
    public static String getSheetCellValueWithCellName(Sheet sheet, String cellName) {
        CellReference cellReference = new CellReference(cellName);
        Row row = sheet.getRow(cellReference.getRow());
        Cell cell = row.getCell(cellReference.getCol());
        return getCellData(cell);
    }

    /**
     * 获得cell单元格的TypeNumber,范围是0~5
     *
     * @param cell
     * @return
     */
    public static int getColumnTypeNumber(Cell cell) {
        if (cell != null) {
            int type = cell.getCellType();
            return type;
        }
        return -1;
    }

    /**
     * 获取指定Sheet页 所有合并单元格数据信息
     *
     * @param sheet
     * @return List<Map<String, String>>
     */
    public static List<Map<String, String>> getSheetRegion(Sheet sheet) {
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        //合并的单元格数量
        int merged = sheet.getNumMergedRegions();
        //预读合并的单元格
        for (int i = 0; i < merged; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            Map<String, String> map = new LinkedHashMap<String, String>();
            int colstart = range.getFirstColumn();
            int colend = range.getLastColumn();
            int rowstart = range.getFirstRow();
            int rowend = range.getLastRow();
            map.put("colstart", colstart + "");
            map.put("colend", colend + "");
            map.put("rowstart", rowstart + "");
            map.put("rowend", rowend + "");
            map.put("field", getCharByNum(colstart));
            map.put("colspan", (colend - colstart + 1) + "");
            map.put("rowspan", (rowend - rowstart + 1) + "");
            map.put("index", rowstart + "");
            list.add(map);
        }
        return list;
    }

    /**
     * 获取sheet中指定column的列宽度,这里的宽度是近似宽度,不是很精确
     *
     * @param sheet
     * @param cloumI
     * @return
     */
    public static int getColumnWidth(Sheet sheet, int cloumI) {
        return new BigDecimal(sheet.getColumnWidth(cloumI) * 37 / 1200).setScale(0, BigDecimal.ROUND_HALF_UP).intValue();
    }

    /**
     * 获取sheet中指定column的列宽度集合,这里的宽度是近似宽度,不是很精确
     *
     * @param sheet
     * @return
     */
    public static List<Integer> getColumnWidths(Sheet sheet) {
        List<Integer> columnWidths = new ArrayList<Integer>();
        int lastCellNum = getColumnNum(sheet);
        for (int i = 0; i < lastCellNum; i++) {
            columnWidths.add(new BigDecimal(sheet.getColumnWidth(i) * 37 / 1200).setScale(0, BigDecimal.ROUND_HALF_UP).intValue());
        }
        return columnWidths;
    }

    /**
     * 获取一个Sheet的冻结信息,包括冻结列和冻结行
     *
     * @param sheet
     * @return
     * @throws Exception
     */
    public static Map<String, Short> getSheetFrazenColAndRow(Sheet sheet) throws Exception {
        Map<String, Short> frazenMap = new HashMap<String, Short>();
        PaneInformation paneInformation = sheet.getPaneInformation();
        if (paneInformation != null) {
            //有多少列是冻结的
            frazenMap.put("freezeCol", paneInformation.getVerticalSplitLeftColumn());
            //有多少行是冻结
            frazenMap.put("freezeRow", paneInformation.getHorizontalSplitTopRow());
        }
        return frazenMap;
    }

    /**
     * 获取单元中值(字符串类型)
     *
     * @param cell
     * @return
     */
    public static String getCellData(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            try {
                switch (cell.getCellType()) {
                    //空白
                    case Cell.CELL_TYPE_BLANK:
                        cellValue = "";
                        break;
                    //数值型 0----日期类型也是数值型的一种
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            short format = cell.getCellStyle().getDataFormat();

                            if (yyyyMMddList.contains(format)) {
                                sFormat = FastDateFormat.getInstance(BaseConstants.DATE_PATTERN);
                            } else if (hhMMssList.contains(format)) {
                                sFormat = FastDateFormat.getInstance("HH:mm:ss");
                            }
                            Date date = cell.getDateCellValue();
                            cellValue = sFormat.format(date);
                        } else {
                            Double numberDate = new BigDecimal(cell.getNumericCellValue()).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue();
                            cellValue = numberDate + "";
                        }
                        break;
                    //字符串型 1
                    case Cell.CELL_TYPE_STRING:
                        cellValue = replaceBlank(cell.getStringCellValue());
                        break;
                    //公式型 2
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cellValue = replaceBlank(cell.getStringCellValue());
                        break;
                    //布尔型 4
                    case Cell.CELL_TYPE_BOOLEAN:
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    //错误 5
                    case Cell.CELL_TYPE_ERROR:
                        cellValue = "!#REF!";
                        break;
                    default:
                        break;
                }
            } catch (Exception e) {
                System.out.println("读取Excel单元格数据出错：" + e.getMessage());
                return cellValue;
            }
        }
        return cellValue;
    }

    public static String replaceBlank(String source) {
        String dest = "";
        if (source != null) {
            Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(source);
            dest = m.replaceAll("");
        }
        return dest;
    }

    /**
     * 给SHEET某一个单元格赋值
     *
     * @param sheet 指定单元格
     * @param rowNum 行号
     * @param cellNum 列号
     * @param value 值
     */
    public static void setCellValue(Sheet sheet, int rowNum, int cellNum, String value) {
        Row row = sheet.getRow(rowNum);
        Cell cell = row.getCell(cellNum);
        if (cell == null) {
            row.createCell(cellNum).setCellValue(value);
        } else {
            cell.setCellValue(value);
        }
    }

    public static void mergedRegion(Sheet sheet) throws Exception {
        //合并的单元格数量
        int merged = sheet.getNumMergedRegions();
        //预读合并的单元格
        for (int i = 0; i < merged; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int y0 = range.getFirstRow();
            int x0 = range.getFirstColumn();
            int y1 = range.getLastRow();
            int x1 = range.getLastColumn();

            String value = getSheetCellValueWithRowIndexAndColIndex(sheet, y0, x0);

            for (int m = y0; m <= y1; m++) {
                for (int n = x0; n <= x1; n++) {
                    setCellValue(sheet, m, n, value);
                }
            }
        }
    }

    /**
     * 生成表头名称,A,B,C,D...
     *
     * @param number
     * @return
     */
    public static String getCharByNum(int number) {
        int index = number / 26 - 1;
        if (index < 0) {
            return (char) (65 + number % 26) + "";
        } else if (index >= 0) {
            return (char) (65 + index) + "" + (char) (65 + number % 26) + "";
        }
        return "@";
    }

    /**
     * 补全String字符串,
     *
     * @param str 字符窜
     * @param len 长度
     * @param pre 补全字符
     * @return 补全之后的字符串
     */
    public static String preFillString(String str, int len, char pre) {
        int length = len - str.length();
        for (int i = 0; i < length; i++) {
            str = pre + str;
        }
        return str;
    }

    /**
     * 获取颜色的HTML表示方式,
     *
     * @param str getHexString()
     * @return
     */
    public static String getColorByHex(String str) {
        String[] hexString = str.split(":");
        String colorRGB = "";
        for (int i = 0; i < hexString.length; i++) {
            hexString[i] = preFillString(hexString[i], 4, '0');
            colorRGB += hexString[i].substring(0, 2);
        }
        if ("000000".equals(colorRGB)) {
            colorRGB = "";
        }
        return colorRGB;
    }

    /**
     * 获取颜色
     *
     * @param shortColor
     * @return
     */
    public static String getColorByShortColor(short shortColor) {
        String returnColor = "";
        for (IndexedColors color : IndexedColors.values()) {
            if (shortColor == color.getIndex()) {
                returnColor = color.toString();
            }
        }
        if ("AUTOMATIC".equals(returnColor)) {
            returnColor = "";
        }
        return returnColor;
    }

    /**
     * 获取Sheet中所有单元格样式合集
     *
     * @param sheet
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> getSheetCellStyleMaps(Sheet sheet) throws Exception {
        List<Map<String, Object>> sheetCellStyles = new ArrayList<Map<String, Object>>();
        int lastRowNum = getRowNum(sheet);
        Row row;
        for (int i = 0; i < lastRowNum; i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int columnNumMax = getColumnNum(sheet);
            for (int j = 0; j < columnNumMax; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                Map<String, Object> cellMap = getCellStyleMap(sheet, cell);
                cellMap.put("y", i);
                cellMap.put("x", j);
                sheetCellStyles.add(cellMap);
            }
        }
        return sheetCellStyles;
    }

    /**
     * 获取Sheet中,某一个Cell的样式,Cell的背景颜色单独去取,借助于HSSFSheet和XSSFSheet
     *
     * @param sheet
     * @param cell
     * @return
     */
    public static Map<String, Object> getCellStyleMap(Sheet sheet, Cell cell) {
        Map<String, Object> cellStyleMap = new HashMap<String, Object>();

        Short alignShort = cell.getCellStyle().getAlignment();
        String alignment = "c";
        if (alignShort == 1) {
            alignment = "l";
        } else if (alignShort == 3) {
            alignment = "r";
        }

        CellStyle cellStyle = cell.getCellStyle();
        Workbook workbook = sheet.getWorkbook();
        Font font = workbook.getFontAt(cellStyle.getFontIndex());
        cellStyleMap.put("fontColor", getColorByShortColor(font.getColor()));
        cellStyleMap.put("fontBold", font.getBoldweight());
        cellStyleMap.put("fontSize", font.getFontHeightInPoints());
        cellStyleMap.put("alignment", alignment);
        try {
            HSSFCellStyle hSSFCellStyle = (HSSFCellStyle) cell.getCellStyle();
            cellStyleMap.put("cellColor", getColorByHex(hSSFCellStyle.getFillForegroundColorColor().getHexString()));
        } catch (Exception e) {
            XSSFCellStyle xSSFCellStyle = (XSSFCellStyle) cell.getCellStyle();
            String xssfCellColor = "";
            if (xSSFCellStyle.getFillBackgroundColorColor() != null) {
                xssfCellColor = xSSFCellStyle.getFillForegroundColorColor().getARGBHex().substring(2);
            }
            xssfCellColor = "000000".equals(xssfCellColor) ? "" : xssfCellColor;
            cellStyleMap.put("cellColor", xssfCellColor);
        }
        return cellStyleMap;
    }

    /**
     * 根据行列生成 单元格
     * @param rowNum
     * @param colNum
     * @param style
     */
    public static void createCell(int rowNum, int colNum, Sheet sheet, CellStyle style){
        //创建所有单元格并生成样式（避免边框样式丢失）
        for (int i = 0; i < rowNum; i++) {
            HSSFRow hssfRow = (HSSFRow) sheet.createRow(i);
            for (int j = 0; j <= colNum ; j++) {
                sheet.setColumnWidth(j, 2766);
                HSSFCell cell = hssfRow.createCell(j);
                cell.setCellStyle(style);
            }
        }
    }

    /**
     * 单元格样式属性
     * @param wb
     * @return
     */
    public static CellStyle getStyle(HSSFWorkbook wb){
        HSSFFont font = (HSSFFont) wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        org.apache.poi.hssf.usermodel.HSSFCellStyle centerstyle = (org.apache.poi.hssf.usermodel.HSSFCellStyle) wb.createCellStyle();
        centerstyle.setFont(font);
        // 左右居中
        centerstyle.setAlignment(org.apache.poi.hssf.usermodel.HSSFCellStyle.ALIGN_CENTER);
        // 上下居中
        centerstyle.setVerticalAlignment(org.apache.poi.hssf.usermodel.HSSFCellStyle.VERTICAL_CENTER);
        //设置自动换行
        centerstyle.setWrapText(true);
        centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);
        centerstyle.setBorderLeft((short) 1);
        centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
        centerstyle.setBorderRight((short) 1);
        centerstyle.setTopBorderColor(HSSFColor.BLACK.index);
        centerstyle.setBorderTop((short) 1);
        // 设置单元格的边框为粗体
        centerstyle.setBorderBottom(org.apache.poi.hssf.usermodel.HSSFCellStyle.BORDER_THIN);
        // 设置单元格的边框颜色．
        centerstyle.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置单元格的背景颜色．
        centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);
        return centerstyle;
    }

    /**
     * 设置单元格字体水平居中
     *wo'r
     * @param wb
     * @param cell
     */
    public void fontCenter(XSSFWorkbook wb, XSSFCell cell) {
        // 设置字体
        XSSFFont font = wb.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        XSSFCellStyle style = wb.createCellStyle();
        //设置底边框;
        style.setBorderBottom(CellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(CellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(CellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(CellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cell.setCellStyle(style);
    }

    /**
     * 获取工作簿对象
     * @return
     */
    public static HSSFWorkbook getWorkbook(){
        HSSFWorkbook workbook = new HSSFWorkbook();
        return workbook;
    }

    /**
     * 获取页
     * @return
     */
    public static HSSFSheet getSheet(String sheetName,HSSFWorkbook workbook){
        // 为excel创建sheet页
        HSSFSheet sheet = workbook.createSheet(sheetName);
        return sheet;
    }

    /**
     * 设置合并后的边框
     * @param cra
     * @param sheet
     * @param wb
     */
    public static void setMerageBorder(CellRangeAddress cra, Sheet sheet,Workbook wb){
        RegionUtil.setBorderBottom(1,cra, sheet, wb);
        RegionUtil.setBorderLeft(1,cra, sheet, wb);
        RegionUtil.setBorderRight(1,cra, sheet, wb);
        RegionUtil.setBorderTop(1,cra, sheet, wb);
    }

    /**
     * 该方法用于传入某实例对象以及对象方法名，通过反射调用该对象的某个get方法
     * @param beanObj
     * @param cell
     * @param filedName
     */
    public static void getProperty(Object beanObj, HSSFCell cell, String filedName) {
        try {
            //获得属性
            Field field = beanObj.getClass().getDeclaredField(filedName);
            Class clazz = beanObj.getClass();
            // 此处判断beanObj,property不为null
            PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
            if (pd != null){
                Method getMethod = pd.getReadMethod();
                Object invoke = getMethod.invoke(beanObj);
                if (null != invoke) {
                    cell.setCellValue(String.valueOf(invoke));
                } else {
                    cell.setCellValue("");
                }
            }
        } catch (IllegalAccessException e) {
            logger.error("非法访问异常");
        } catch (IllegalArgumentException e) {
            logger.error("非法参数异常");
        } catch (InvocationTargetException e) {
            logger.error("调用目标异常");
        } catch (IntrospectionException e) {
            logger.error("内省异常");
        } catch (NoSuchFieldException e) {
            logger.error("变量没有找到");
        }
    }

    /**
     * 单个对象的某个键的值
     *
     * @param obj 对象
     * @param key 键
     *
     * @return Object 键在对象中所对应得值 没有查到时返回空字符串
     */
    public static Object getValueByKey(Object obj, String key) {
        // 得到类对象
        Class userCla = (Class) obj.getClass();
        // 得到类中的所有属性集合
        Field[] fs = userCla.getDeclaredFields();
        for (int i = 0; i < fs.length; i++) {
            Field f = fs[i];
            f.setAccessible(true);
            try {
                if (f.getName().endsWith(key)) {
                    return f.get(obj);
                }
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return "";
    }

    public static void main(String[] args) throws Exception {
        Workbook workbook = getExcelWorkbook("D:/myExcel.xls");
        Sheet sheet = getSheetByNum(workbook, 1);
        System.out.println(getSheetCellStyleMaps(sheet));
    }
}
